﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
//using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace SuperSqlOperation.Sqlite
{
    /// <summary>
    /// 改变特定数据
    /// </summary>
    static public class UpdateSqlExecute
    {
        public static async Task<int> UpdateExecute<T>(this IDbConnection conn, Dictionary<string, object> columns, Dictionary<string, object> conds, string linkStr)
        {
            linkStr = " " + linkStr + " ";
            List<T> dataList = new List<T>();
            StringBuilder setStr = new StringBuilder();
            StringBuilder whereStr = new StringBuilder();
            List<SQLiteParameter> setPar = new List<SQLiteParameter>();
            List<SQLiteParameter> wherePar = new List<SQLiteParameter>();
            Type t = typeof(T);
            PropertyInfo[] propertyInfo = t.GetProperties();
            string tableName = t.GetDataTableNameByAttr();
             
            foreach (var column in columns)
            {
                List<PropertyInfo> tProps = propertyInfo.ToList()
                    .Where(p => p.Name == column.Key).ToList();
                if (tProps.Count == 0) continue;
                PropertyInfo property = tProps.First();

                if (property.GetDataKeyByAttr()) continue;      //更新数据不对主键操作
                if (property.GetDataIgnoreByAttr()) continue;   //过滤掉特性定义的需要忽略的属性
                if (!property.GetDataWriteByAttr()) continue;   //过滤掉特性定义的是否可写的属性 定义为false则忽略 
                if (property.PropertyType.IsConstructedGenericType ||
                    (!property.PropertyType.IsPrimitive && !property.PropertyType.Equals(typeof(string)) &&
                    property.PropertyType.IsClass)) continue;

                string columnName = column.Key;
                object columnValue = column.Value;
                setStr.Append(columnName + " = @" + columnName + ",");
                setPar.Add(new SQLiteParameter("@" + columnName, columnValue));
            }
            foreach (var cond in conds)
            {
                List<PropertyInfo> tProps = propertyInfo.ToList()
                    .Where(p => p.Name == cond.Key).ToList();
                if (tProps.Count == 0) continue;
                PropertyInfo property = tProps.First();
                if (property.GetDataIgnoreByAttr()) continue;   //过滤掉特性定义的需要忽略的属性
                if (!property.GetDataWriteByAttr()) continue;   //过滤掉特性定义的是否可写的属性 定义为false则忽略 

                string columnName = cond.Key;
                object columnValue = cond.Value;
                whereStr.Append(columnName + "= @" + columnName + linkStr);
                wherePar.Add(new SQLiteParameter("@" + columnName, columnValue));
            }

            //-----------------
            string sqlCommandText = string.Format("UPDATE {0} SET {1} WHERE {2} ",
                    tableName,
                    setStr.ToString().TrimEnd(','),
                    whereStr.ToString().Substring(0, whereStr.ToString().LastIndexOf(linkStr)));
            //-----------------
            int insertId = 0;
            using (conn)
            {
                try
                {
                    SQLiteCommand comm = conn.CreateCommand() as SQLiteCommand;
                    //  UPDATE table_name SET column1 = value1, column2 = value2  WHERE some_column = some_value;
                    comm.CommandText = sqlCommandText;
                    comm.Parameters.AddRange(setPar.ToArray());
                    comm.Parameters.AddRange(wherePar.ToArray());

                    conn.Open();
                    //insertId = await Task.Run(() => comm.ExecuteNonQuery());
                    insertId = await comm.ExecuteNonQueryAsync();
                    conn.Close();
                }
                catch (Exception)
                {
                    conn.Close();
                    insertId =-404;
                }  
            }
            return insertId;
        }

        public static async Task<int> UpdateExecute<T>(this IDbConnection conn, T model, Dictionary<string, object> conds, string linkStr)
        {
            linkStr = " " + linkStr + " ";
            List<T> dataList = new List<T>();
            StringBuilder setStr = new StringBuilder();
            StringBuilder whereStr = new StringBuilder();
            List<SQLiteParameter> setPar = new List<SQLiteParameter>();
            List<SQLiteParameter> wherePar = new List<SQLiteParameter>();
            Type t = model.GetType();
            PropertyInfo[] propertyInfo = t.GetProperties();
            string tableName = t.GetDataTableNameByAttr();

            foreach (PropertyInfo property in propertyInfo)
            {
                if (property.GetDataKeyByAttr()) continue;      //更新数据不对主键操作
                if (property.GetDataIgnoreByAttr()) continue;   //过滤掉特性定义的需要忽略的属性
                if (!property.GetDataWriteByAttr()) continue;   //过滤掉特性定义的是否可写的属性 定义为false则忽略 
                if (property.PropertyType.IsConstructedGenericType ||
                    (!property.PropertyType.IsPrimitive && !property.PropertyType.Equals(typeof(string)) &&
                    property.PropertyType.IsClass)) continue;

                string columnName = property.GetDataColumnByAttr();
                var columnValue = property.GetValue(model) == null ? DBNull.Value : property.GetValue(model);
                setStr.Append(columnName + " = @" + columnName + ",");
                setPar.Add(new SQLiteParameter("@" + columnName, columnValue));
            }

            //foreach (var column in columns)
            //{
            //    List<PropertyInfo> tProps = propertyInfo.ToList()
            //        .Where(p => p.Name == column.Key).ToList();
            //    if (tProps.Count == 0) continue;
            //    PropertyInfo property = tProps.First();

            //    if (property.GetDataKeyByAttr()) continue;      //更新数据不对主键操作
            //    if (property.GetDataIgnoreByAttr()) continue;   //过滤掉特性定义的需要忽略的属性
            //    if (!property.GetDataWriteByAttr()) continue;   //过滤掉特性定义的是否可写的属性 定义为false则忽略 
            //    if (property.PropertyType.IsConstructedGenericType ||
            //        (!property.PropertyType.IsPrimitive && !property.PropertyType.Equals(typeof(string)) &&
            //        property.PropertyType.IsClass)) continue;

            //    string columnName = column.Key;
            //    object columnValue = column.Value;
            //    setStr.Append(columnName + " = @" + columnName + ",");
            //    setPar.Add(new SQLiteParameter("@" + columnName, columnValue));
            //}

            foreach (var cond in conds)
            {
                List<PropertyInfo> tProps = propertyInfo.ToList()
                    .Where(p => p.Name == cond.Key).ToList();
                if (tProps.Count == 0) continue;
                PropertyInfo property = tProps.First();
                if (property.GetDataIgnoreByAttr()) continue;   //过滤掉特性定义的需要忽略的属性
                if (!property.GetDataWriteByAttr()) continue;   //过滤掉特性定义的是否可写的属性 定义为false则忽略 

                string columnName = cond.Key;
                object columnValue = cond.Value;
                whereStr.Append(columnName + "= @" + columnName + linkStr);
                wherePar.Add(new SQLiteParameter("@" + columnName, columnValue));
            }

            //-----------------
            string sqlCommandText = string.Format("UPDATE {0} SET {1} WHERE {2} ",
                    tableName,
                    setStr.ToString().TrimEnd(','),
                    whereStr.ToString().Substring(0, whereStr.ToString().LastIndexOf(linkStr)));
            //-----------------
            int insertId = 0;
            using (conn)
            {
                try
                {
                    SQLiteCommand comm = conn.CreateCommand() as SQLiteCommand;
                    //  UPDATE table_name SET column1 = value1, column2 = value2  WHERE some_column = some_value;
                    comm.CommandText = sqlCommandText;
                    comm.Parameters.AddRange(setPar.ToArray());
                    comm.Parameters.AddRange(wherePar.ToArray());

                    conn.Open();
                    //insertId = await Task.Run(() => comm.ExecuteNonQuery());
                    insertId = await comm.ExecuteNonQueryAsync();
                    conn.Close();
                }
                catch (Exception)
                {
                    conn.Close();
                    insertId =-404;
                } 
            }
            return insertId;
        }

    }
}
